package com.sdry.web.controller.item;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import com.sdry.entity.item.Item;
import com.sdry.utils.ResponseUtil;
import com.sdry.utils.StringUtil;

/**
 * @author tdd
 * @version V1.0
 * @ClassName: Item
 * @Description:
 * @date 2018/9/3 9:57
 */
@Controller
@RequestMapping("/aProjectBase")
public class ItemController {


    public static SQLServerConnection conn;

    /**
     * 连接第三方数据库查询所有项目名称
     * @return项目名称集合
     */
    public static List<List<String>>  getconn(String sqlSelect,List<String> name) {
    	List<List<String>> list = new ArrayList<>();

        Statement sql;
        ResultSet rs;
        try{

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch(Exception e){
            System.out.println(e);
        }
        try{
            String url="jdbc:sqlserver://119.90.97.7;DatabaseName=TSFM";
            String user="cHpfDoc";
            String password="6ygUE@kj3id89h8u7_0";
            conn=(SQLServerConnection) DriverManager.getConnection(url,user,password);

            sql=conn.createStatement();
            rs=sql.executeQuery(sqlSelect);
            while(rs.next()){
            	List<String> listBean = new ArrayList<>();
            	for (int i = 0; i < name.size(); i++) {
            		//System.out.println(rs.getString("Title"));
            		listBean.add(rs.getString(name.get(i)));
				}
            	list.add(listBean);
            	listBean = null;
            }

        }catch(Exception e){
            System.out.println(e);
        }


        //return conn;
        return list;
    }
  
    /**
     * 根据条件查询专项基金
     * @param response 响应
     * @param template 条件
     * @throws Exception 异常
     */
    @RequestMapping("/selectASpecialBase")
    public void selectASpecialBaseByExample(HttpServletResponse response,Item itemTJ) throws Exception {
    	String sqlSelect = "select * from ASpecialBase where FlagDeleted = 'False'";
    	if (StringUtil.isNotEmpty(itemTJ.getTitle())) {
    		sqlSelect += "and Title like '"+"%"+itemTJ.getTitle()+"%"+"'";
		}
    	if (StringUtil.isNotEmpty(itemTJ.getAlias())) {
    		sqlSelect += "and Alias = '"+"%"+itemTJ.getAlias()+"%"+"'";
    	}
    	if (StringUtil.isNotEmpty(itemTJ.getCode())) {
    		sqlSelect += "and Code = '"+"%"+itemTJ.getCode()+"%"+"'";
    	}
    	System.out.println(sqlSelect);
    	List<String> name = new ArrayList<>();
    	name.add("ID");
    	name.add("Title");
    	name.add("Alias");
    	name.add("Code");
		List<List<String>> list = getconn(sqlSelect,name);
		List<Item> items  = new ArrayList<>();
    	for (int i = 0; i < list.size(); i++) {
			List<String> list2 = list.get(i);
			Item item = new Item();
			if(list2.size() >= 4){
				item.setID(list2.get(0));
				item.setTitle(list2.get(1));
				item.setAlias(list2.get(2));
				item.setCode(list2.get(3));
				items.add(item);
			}
		}
        JSONArray json = JSONArray.fromObject(items);
        ResponseUtil.write(response, json);
    }
    /**
     * 根据条件查询公益项目
     * @param response 响应
     * @param template 条件
     * @throws Exception 异常
     */
    @RequestMapping("/selectAProjectBase")
    public void selectAProjectBaseByExample(HttpServletResponse response,String id,Item itemTJ) throws Exception {
    	String sqlSelect = "select * from AProjectBase where FlagDeleted = 'False' and SpecialID = '"+id+"'";
    	if (StringUtil.isNotEmpty(itemTJ.getTitle())) {
    		sqlSelect += "and Title like '"+"%"+itemTJ.getTitle()+"%"+"'";
		}
    	if (StringUtil.isNotEmpty(itemTJ.getAlias())) {
    		sqlSelect += "and Alias = '"+"%"+itemTJ.getAlias()+"%"+"'";
    	}
    	if (StringUtil.isNotEmpty(itemTJ.getCode())) {
    		sqlSelect += "and Code = '"+"%"+itemTJ.getCode()+"%"+"'";
    	}
    	List<String> name = new ArrayList<>();
    	name.add("ID");
    	name.add("Title");
    	name.add("Alias");
    	name.add("Code");
    	List<List<String>> list = getconn(sqlSelect,name);
    	List<Item> items  = new ArrayList<>();
    	for (int i = 0; i < list.size(); i++) {
			List<String> list2 = list.get(i);
			Item item = new Item();
			if(list2.size() >= 4){
				item.setID(list2.get(0));
				item.setTitle(list2.get(1));
				item.setAlias(list2.get(2));
				item.setCode(list2.get(3));
				items.add(item);
			}
		}
    	JSONArray json = JSONArray.fromObject(items);
    	ResponseUtil.write(response, json);
    }
    /**
     * 根据条件查询项目活动
     * @param response 响应
     * @param template 条件
     * @throws Exception 异常
     */
    @RequestMapping("/selectAProjectActivity")
    public void selectAProjectActivityByExample(HttpServletResponse response,String id,Item itemTJ) throws Exception {
    	String sqlSelect = "select * from AProjectActivity where FlagDeleted = 'False' and ProjectID = '"+id+"'";
    	if (StringUtil.isNotEmpty(itemTJ.getTitle())) {
    		sqlSelect += "and Title like '"+"%"+itemTJ.getTitle()+"%"+"'";
		}
    	List<String> name = new ArrayList<>();
    	name.add("Title");
    	List<List<String>> list = getconn(sqlSelect,name);
    	List<Item> items  = new ArrayList<>();
    	for (int i = 0; i < list.size(); i++) {
			List<String> list2 = list.get(i);
			Item item = new Item();
			if(list2.size() > 0){
				item.setTitle(list2.get(0));
				items.add(item);
			}
		}
    	JSONArray json = JSONArray.fromObject(items);
    	ResponseUtil.write(response, json);
    }




}
